using System;
using System.Data;
using System.Collections;
using APP.QUANLY.Common;
using RMAS.DAL;

namespace RMAS.DAL
{
    /// <summary>
    /// Summary description for QT_TABLERENT.
    /// </summary>
    public class QT_TABLERENTDao
    {
        private string _SQLSelectByKey;
        private string _SQLUpdate;
        private string _SQLInsert;
        private string _SQLDelete;
        private string _SQLSelectAll;

        #region Properties

        public IDbConnection _dbConnection;

        public IDbConnection DbConnection
        {
            get { return _dbConnection; }
            set { _dbConnection = value; }
        }

        #endregion

        #region Constructor

        public QT_TABLERENTDao()
        {
            _dbConnection = null;
            InitSqlQuery();
        }

        public QT_TABLERENTDao(IDbConnection con)
        {
            _dbConnection = con;
            InitSqlQuery();
        }

        #endregion


        private void ToLowerQuery()
        {
            _SQLDelete = _SQLDelete.ToLower();
            _SQLUpdate = _SQLUpdate.ToLower();
            _SQLInsert = _SQLInsert.ToLower();
            _SQLSelectByKey = _SQLSelectByKey.ToLower();
        }

        private void ToUpperQuery()
        {
            _SQLDelete = _SQLDelete.ToUpper();
            _SQLUpdate = _SQLUpdate.ToUpper();
            _SQLInsert = _SQLInsert.ToUpper();
            _SQLSelectByKey = _SQLSelectByKey.ToUpper();
        }

        #region InitSqlQuery
        private void InitSqlQuery()
        {
            string query;

            query = @"SELECT ID, TableID, TableName, ExBillID, TimeStart, TimeEnd, TimeUse, TableMoney, ServiceMoney, MoneyBeforeChange, ChangeTable, TimeMinutePause, UserName, IDPOS FROM QT_TABLERENT WHERE (ID = @ID)";
            _SQLSelectByKey = query;

            query = @"UPDATE QT_TABLERENT SET TableID = @TableID, TableName = @TableName, ExBillID = @ExBillID, TimeStart = @TimeStart, TimeEnd = @TimeEnd, TimeUse = @TimeUse, TableMoney = @TableMoney, ServiceMoney = @ServiceMoney, MoneyBeforeChange = @MoneyBeforeChange, ChangeTable = @ChangeTable, TimeMinutePause = @TimeMinutePause, UserName = @UserName, IDPOS = @IDPOS WHERE (ID = @ID)";
            _SQLUpdate = query;

            query = @"INSERT INTO QT_TABLERENT VALUES (@ID, @TableID, @TableName, @ExBillID, @TimeStart, @TimeEnd, @TimeUse, @TableMoney, @ServiceMoney, @MoneyBeforeChange, @ChangeTable, @TimeMinutePause, @UserName, @IDPOS)";
            _SQLInsert = query;

            query = @"DELETE FROM QT_TABLERENT WHERE (ID = @ID)";
            _SQLDelete = query;

            query = @"SELECT ID, TableID, TableName, ExBillID, TimeStart, TimeEnd, TimeUse, TableMoney, ServiceMoney, MoneyBeforeChange, ChangeTable, TimeMinutePause, UserName, IDPOS FROM QT_TABLERENT";
            _SQLSelectAll = query;
        }
        #endregion
        public void LoadByCommandText(string commandtext, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = commandtext;

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        #region Save & Delete
        private void Save(QT_TABLERENT obj, IDbTransaction trans, bool isExisting)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command = _dbConnection.CreateCommand();

                if (!isExisting) { command.CommandText = _SQLInsert + ";"; }
                else { command.CommandText = _SQLUpdate + ";"; }

                DalTools.addDbDataParameter(command, "ID", obj.ID, DbType.Int32);
                DalTools.addDbDataParameter(command, "TableID", obj.TableID, DbType.String);
                DalTools.addDbDataParameter(command, "TableName", obj.TableName, DbType.String);
                DalTools.addDbDataParameter(command, "ExBillID", obj.ExBillID, DbType.String);
                DalTools.addDbDataParameter(command, "TimeStart", obj.TimeStart, DbType.DateTime);
                DalTools.addDbDataParameter(command, "TimeEnd", obj.TimeEnd, DbType.DateTime);
                DalTools.addDbDataParameter(command, "TimeUse", obj.TimeUse, DbType.DateTime);
                DalTools.addDbDataParameter(command, "TableMoney", obj.TableMoney, DbType.Decimal);
                DalTools.addDbDataParameter(command, "ServiceMoney", obj.ServiceMoney, DbType.Decimal);
                DalTools.addDbDataParameter(command, "MoneyBeforeChange", obj.MoneyBeforeChange, DbType.Decimal);
                DalTools.addDbDataParameter(command, "ChangeTable", obj.ChangeTable, DbType.String);
                DalTools.addDbDataParameter(command, "TimeMinutePause", obj.TimeMinutePause, DbType.Int32);
                DalTools.addDbDataParameter(command, "UserName", obj.UserName, DbType.String);
                DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.String);

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Delete(QT_TABLERENT obj, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                //throw new DalException("Connection has not been set.");
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = _SQLDelete + ";";

                DalTools.addDbDataParameter(command, "ID", obj.ID, DbType.Int32);

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Insert(QT_TABLERENT obj, IDbTransaction trans)
        {
            Save(obj, trans, false);
        }
        public void Update(QT_TABLERENT obj, IDbTransaction trans)
        {
            Save(obj, trans, true);
        }
        #endregion

        #region Command Execute & Read Properties

        private QT_TABLERENTList SelectListByCommand(IDbCommand command)
        {
            QT_TABLERENTList list = new QT_TABLERENTList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                QT_TABLERENT obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        obj = new QT_TABLERENT();
                        ReadProperties(obj, reader);
                        list.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private QT_TABLERENTList SelectListByCommand(IDbCommand command, IDbTransaction trans)
        {
            QT_TABLERENTList list = new QT_TABLERENTList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                QT_TABLERENT obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command.Transaction = trans;
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        obj = new QT_TABLERENT();
                        ReadProperties(obj, reader);
                        list.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private void DeleteListByCommand(IDbCommand command, IDbTransaction trans)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                if (trans != null) { command.Transaction = trans; }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void ReadProperties(QT_TABLERENT obj, IDataReader reader)
        {
            try
            {
                if (!(reader["ID"] is DBNull))
                {
                    obj.ID = (Int32)reader["ID"];
                }
                if (!(reader["TableID"] is DBNull))
                {
                    obj.TableID = (string)reader["TableID"];
                }
                if (!(reader["TableName"] is DBNull))
                {
                    obj.TableName = (string)reader["TableName"];
                }
                if (!(reader["ExBillID"] is DBNull))
                {
                    obj.ExBillID = (string)reader["ExBillID"];
                }
                if (!(reader["TimeStart"] is DBNull))
                {
                    obj.TimeStart = (DateTime)reader["TimeStart"];
                }
                if (!(reader["TimeEnd"] is DBNull))
                {
                    obj.TimeEnd = (DateTime)reader["TimeEnd"];
                }
                if (!(reader["TimeUse"] is DBNull))
                {
                    obj.TimeUse = (DateTime)reader["TimeUse"];
                }
                if (!(reader["TableMoney"] is DBNull))
                {
                    obj.TableMoney = (decimal)reader["TableMoney"];
                }
                if (!(reader["ServiceMoney"] is DBNull))
                {
                    obj.ServiceMoney = (decimal)reader["ServiceMoney"];
                }
                if (!(reader["MoneyBeforeChange"] is DBNull))
                {
                    obj.MoneyBeforeChange = (decimal)reader["MoneyBeforeChange"];
                }
                if (!(reader["ChangeTable"] is DBNull))
                {
                    obj.ChangeTable = (string)reader["ChangeTable"];
                }
                if (!(reader["TimeMinutePause"] is DBNull))
                {
                    obj.TimeMinutePause = (Int32)reader["TimeMinutePause"];
                }
                if (!(reader["UserName"] is DBNull))
                {
                    obj.UserName = (string)reader["UserName"];
                }
                if (!(reader["IDPOS"] is DBNull))
                {
                    obj.IDPOS = (string)reader["IDPOS"];
                }
            }
            catch (Exception ex)
            {
                throw new DalException("Failed to read properties from DataReader.", ex);
            }
        }
        #endregion

        public QT_TABLERENT SelectByKey(Int32 ID)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
            DalTools.addDbDataParameter(command, "ID", ID, DbType.Int32);
            QT_TABLERENTList list = SelectListByCommand(command);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public QT_TABLERENT SelectByKey(Int32 ID, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
            DalTools.addDbDataParameter(command, "ID", ID, DbType.Int32);
            QT_TABLERENTList list = SelectListByCommand(command, trans);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public QT_TABLERENTList SelectAll()
        {
            IDbCommand command = _dbConnection.CreateCommand();
            if (!StaticVar.IsServer)
            {
                _SQLSelectAll = _SQLSelectAll + " WHERE IDPOS = @IDPOS";
                DalTools.addDbDataParameter(command, "IDPOS", StaticVar.CPUID, DbType.String);
            }
            command.CommandText = _SQLSelectAll;
            QT_TABLERENTList list = SelectListByCommand(command);
            return list;
        }
        public QT_TABLERENTList SelectAll(IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            if (!StaticVar.IsServer)
            {
                _SQLSelectAll = _SQLSelectAll + " WHERE IDPOS = @IDPOS";
                DalTools.addDbDataParameter(command, "IDPOS", StaticVar.CPUID, DbType.String);
            }
            command.CommandText = _SQLSelectAll;
            QT_TABLERENTList list = SelectListByCommand(command, trans);
            return list;
        }
        public QT_TABLERENTList SelectByCommand(string Command)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = Command;
            QT_TABLERENTList list = SelectListByCommand(command);
            return list;
        }

        public QT_TABLERENTList SelectByCondition(QT_TABLERENT obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM QT_TABLERENT " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            QT_TABLERENTList list = SelectListByCommand(command, trans);
            return list;
        }

        public QT_TABLERENTList SelectByCondition(QT_TABLERENT obj)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM QT_TABLERENT " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            QT_TABLERENTList list = SelectListByCommand(command);
            return list;
        }
        private void DeleteByCondition(QT_TABLERENT obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "DELETE FROM QT_TABLERENT " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DeleteListByCommand(command, trans);
        }

        #region Make Condition Query & Fill Param from Condition to Command
        private string GetCondtionQuery(QT_TABLERENT obj)
        {
            if (obj == null) { return ""; }
            string s = "";
            if (obj.IsSetID)
            {
                s += " AND ID = @ID ";
            }
            if (obj.IsSetTableID)
            {
                s += " AND TableID = @TableID ";
            }
            if (obj.IsSetTableName)
            {
                s += " AND TableName = @TableName ";
            }
            if (obj.IsSetExBillID)
            {
                s += " AND ExBillID = @ExBillID ";
            }
            if (obj.IsSetTimeStart)
            {
                s += " AND TimeStart = @TimeStart ";
            }
            if (obj.IsSetTimeEnd)
            {
                s += " AND TimeEnd = @TimeEnd ";
            }
            if (obj.IsSetTimeUse)
            {
                s += " AND TimeUse = @TimeUse ";
            }
            if (obj.IsSetTableMoney)
            {
                s += " AND TableMoney = @TableMoney ";
            }
            if (obj.IsSetServiceMoney)
            {
                s += " AND ServiceMoney = @ServiceMoney ";
            }
            if (obj.IsSetMoneyBeforeChange)
            {
                s += " AND MoneyBeforeChange = @MoneyBeforeChange ";
            }
            if (obj.IsSetChangeTable)
            {
                s += " AND ChangeTable = @ChangeTable ";
            }
            if (obj.IsSetTimeMinutePause)
            {
                s += " AND TimeMinutePause = @TimeMinutePause ";
            }
            if (obj.IsSetUserName)
            {
                s += " AND UserName = @UserName ";
            }
            if (obj.IsSetIDPOS)
            {
                s += " AND IDPOS = @IDPOS ";
            }
            if (s.Length > 0)
            {
                s = " WHERE " + s.Substring(4);
                return s;
            }
            return "";
        }

        private void FillParamToCommand(IDbCommand command, QT_TABLERENT obj)
        {
            if (obj == null) { return; }
            if (obj.IsSetID)
            {
                DalTools.addDbDataParameter(command, "ID", obj.ID, DbType.Int32);
            }
            if (obj.IsSetTableID)
            {
                DalTools.addDbDataParameter(command, "TableID", obj.TableID, DbType.String);
            }
            if (obj.IsSetTableName)
            {
                DalTools.addDbDataParameter(command, "TableName", obj.TableName, DbType.String);
            }
            if (obj.IsSetExBillID)
            {
                DalTools.addDbDataParameter(command, "ExBillID", obj.ExBillID, DbType.String);
            }
            if (obj.IsSetTimeStart)
            {
                DalTools.addDbDataParameter(command, "TimeStart", obj.TimeStart, DbType.DateTime);
            }
            if (obj.IsSetTimeEnd)
            {
                DalTools.addDbDataParameter(command, "TimeEnd", obj.TimeEnd, DbType.DateTime);
            }
            if (obj.IsSetTimeUse)
            {
                DalTools.addDbDataParameter(command, "TimeUse", obj.TimeUse, DbType.DateTime);
            }
            if (obj.IsSetTableMoney)
            {
                DalTools.addDbDataParameter(command, "TableMoney", obj.TableMoney, DbType.Decimal);
            }
            if (obj.IsSetServiceMoney)
            {
                DalTools.addDbDataParameter(command, "ServiceMoney", obj.ServiceMoney, DbType.Decimal);
            }
            if (obj.IsSetMoneyBeforeChange)
            {
                DalTools.addDbDataParameter(command, "MoneyBeforeChange", obj.MoneyBeforeChange, DbType.Decimal);
            }
            if (obj.IsSetChangeTable)
            {
                DalTools.addDbDataParameter(command, "ChangeTable", obj.ChangeTable, DbType.String);
            }
            if (obj.IsSetTimeMinutePause)
            {
                DalTools.addDbDataParameter(command, "TimeMinutePause", obj.TimeMinutePause, DbType.Int32);
            }
            if (obj.IsSetUserName)
            {
                DalTools.addDbDataParameter(command, "UserName", obj.UserName, DbType.String);
            }
            if (obj.IsSetIDPOS)
            {
                DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.String);
            }
        }
        #endregion
    }
}